package com.nuoniu.sibanyun.mapper.provider;

import com.nuoniu.sibanyun.common.exception.NuoNiuException;
import com.nuoniu.sibanyun.common.lang.StringUtils;
import com.nuoniu.sibanyun.entity.dto.DynamicReportDto;
import com.nuoniu.sibanyun.entity.erp.ErpReportDesign;
import org.apache.ibatis.jdbc.SQL;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName ReportDesignDynamicProvider
 * @Description TODO
 * @Authpr baiqiang
 * @Date 2021/2/2 15:11
 **/
public class ReportDesignDynamicProvider {

    /** 校验 数字 用逗号 隔开 的字符串 */
    private static final String STR_ENG_PATTERN="^\\d+(,\\d+)*$";

    private static final  String ID_FIELD = "Id";
    private static final  String NAME_FIELD = "Name";

    /**
     * 有DataSource的单据
     */
    private final String[] dataSourceVoucherArr = {"PurchaseInvoice","DelegateInvoice","PuArrival","DelegateArrival","SaleOrder","SampleOrder","RRS","DelegateRRS","ProductionWork","DelegateProductionWork","InventoryAllocation"};

    /**
     * 没有 业务类型的 动态单据设计
     */
    private final String[] noBusinessVoucherArr = {"NSSaleDispatchVoucher","AreaCalculation","InventoryAllocation","ReceiveVoucher", "PaymentVoucher", "ReceivableVoucher", "PayableVoucher", "OtherReceivableVoucher", "OtherPayableVoucher","ProcedurePrice","Contract"};

    /**
    /**
     * 明细 动态分页查询
     * @param
     * @return
     */
    public String detailReportDynamicSql(DynamicReportDto dynamicReportDto) {
        SQL sql = new SQL();
        selectDetail(sql,dynamicReportDto);
        return  sql.toString();
    }

    private void selectDetail(SQL sql,DynamicReportDto dynamicReportDto){
        List<ErpReportDesign> erpReportDesigns = dynamicReportDto.getErpReportDesignList();
        String someBusinessType = dynamicReportDto.getSomeBusinessType();
        Set<String> selectSet =  new HashSet<>();
        Set<String> whereSet = new HashSet<>();
        Set<String> orderFieldSet = new HashSet<>();
        HashMap<Integer,HashMap<String,String>> tableLevelMap = new HashMap<>();


        if (null == erpReportDesigns || erpReportDesigns.size() == 0)throw new NuoNiuException("参数错误");
        ErpReportDesign erpReportDesign = erpReportDesigns.get(0);
        String reportCode = erpReportDesign.getReportCode();
        String[] reportCodeSplit = reportCode.split("_");
        String  voucherTypeCode = reportCodeSplit[0];
        //1.筛选 开启的条件 和 显示的字段
        List<ErpReportDesign> filterDesigns = erpReportDesigns.stream().filter(f->f.getOnSearch() || f.getOnShow()).collect(Collectors.toList());



        filterDesigns.forEach(item ->{
            String tableNameStr = item.getTableName().trim();
            String columnType = item.getColumnType();
            String code = item.getCode();
            Integer tableLevel = item.getTableLevel();
            Boolean onShow = item.getOnShow();
            Boolean onSearch = item.getOnSearch();
            String value = item.getValue();
            Object[] rangDateVal = item.getRangDateVal();

            String tableAlias = StringUtils.substringAfterLast(tableNameStr," ");

//            String[] tableNameSplit = tableNameStr.split(" ");
//            String tableAlias = tableNameSplit[1];
            String uncamelCase = StringUtils.uncamelCase(code);

            HashMap<String,String> tableMap = tableLevelMap.get(tableLevel);
            if (null == tableMap){
                tableMap = new HashMap<>();
                tableMap.put("tableName",tableNameStr);
                tableMap.put("joinOn",item.getJoinOn());
                tableMap.put("joinType",item.getJoinType());
                tableLevelMap.put(tableLevel,tableMap);
            }

            String showCode = tableAlias + "_" + code;

            boolean valueNotBlank = StringUtils.isNotBlank(value);
            //a.查询字段
            if(onShow){
                if(("模糊搜索".equals(columnType) ||  "搜索选择".equals(columnType) || "查询下拉".equals(columnType))){
                    String nameField = idFieldToNameField(code);
                    String uncamelCaseName = StringUtils.uncamelCase(nameField);
                    String showNameField =  tableAlias + "_" + nameField;
                    if(StringUtils.isBlank(uncamelCaseName)){
                        uncamelCaseName = uncamelCase;
                    }
                    selectSet.add(tableAlias +"."+ uncamelCaseName +" as "+ showNameField);
                    orderFieldSet.add(showNameField);
                    selectSet.add(tableAlias +"."+ uncamelCase +" as "+ showCode);
                }else{
                    selectSet.add(tableAlias +"."+ uncamelCase +" as "+ showCode);
                }
                orderFieldSet.add(showCode);
            }
            //b.查询条件
            if(onSearch){
                if( valueNotBlank){
                    if("文本".equals(columnType) || "区域".equals(columnType) || "快速文本".equals(columnType) ){
                        whereSet.add(tableAlias +"."+ uncamelCase +" LIKE CONCAT('%',#{paramMap."+showCode+"},'%')");
                    }else if("是否".equals(columnType)) {
                        if("false".equals(value)){
                            whereSet.add("("+tableAlias +"."+ uncamelCase +" = 0 OR " +tableAlias +"."+ uncamelCase +" IS NULL)");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" = 1");
                        }
                    }else {
                        whereSet.add(tableAlias +"."+ uncamelCase +" =  #{paramMap."+showCode+"}");
                    }
                }else if(("时间".equals(columnType) || "日期".equals(columnType) || "搜索模糊范围".equals(columnType)) &&  null != rangDateVal && rangDateVal.length == 2 ){
                    ArrayList rangDate = (ArrayList)rangDateVal[1];
                    String startDate = String.valueOf(rangDate.get(0));
                    String endDate =String.valueOf(rangDate.get(1));
                    if(StringUtils.isNoneBlank(startDate)){
                        if("搜索模糊范围".equals(columnType)){
                            whereSet.add("(" + tableAlias +"."+ uncamelCase +" >= #{paramMap."+showCode+"_startDate} OR " + tableAlias +"."+ uncamelCase +" LIKE CONCAT('',#{paramMap."+showCode+"_startDate},'%'))");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" >= #{paramMap."+showCode+"_startDate}");
                        }
                    }
                    if(StringUtils.isNoneBlank(endDate)){
                        if("搜索模糊范围".equals(columnType)){
                            whereSet.add("(" + tableAlias +"."+ uncamelCase +" <= #{paramMap."+showCode+"_endDate} OR " + tableAlias +"."+ uncamelCase +" LIKE CONCAT('',#{paramMap."+showCode+"_endDate},'%'))");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" <= #{paramMap."+showCode+"_endDate}");
                        }
                    }
                }
            }
        } );


        // 3.组装主表
        HashMap<String, String> mainTableMap = tableLevelMap.get(1);
        int mapSize =  tableLevelMap.size() + 1;
        String mainTableNameStr = mainTableMap.get("tableName");
        String mianTableAlias =  mainTableNameStr.split(" ")[1];
        sql.FROM(mainTableNameStr);

        sql.WHERE(mianTableAlias + ".company_id = #{companyId} AND "+ mianTableAlias + ".account_id = #{accountId}");
        //查询ID
        sql.SELECT(mianTableAlias + ".id");
        // 4.组件关联表  tableLevel 必须是从2 开始
        for (int i = 2; i < mapSize ; i++) {
            HashMap<String, String> map = tableLevelMap.get(i);
            String joinType = map.get("joinType");
            String joinOn = map.get("joinOn");
            String tableName = map.get("tableName");
            switch (joinType){
                case "INNER JOIN":sql.INNER_JOIN(tableName + " ON " + joinOn);break;
                case "LEFT JOIN": sql.LEFT_OUTER_JOIN(tableName + " ON " + joinOn);break;
                case "RIGHT JOIN":sql.RIGHT_OUTER_JOIN(tableName + " ON " + joinOn);break;
            }
        }

        // 主表
        HashMap<String, String> map = tableLevelMap.get(2);
        String tableName = map.get("tableName");
        String tableAlias =  tableName.split(" ")[1];
        /******************** 处理 指定 业务类型 开始***********************/
        List<String> noBusinessVoucherList = Arrays.asList(noBusinessVoucherArr);
        if (StringUtils.isNotBlank(someBusinessType)  && !noBusinessVoucherList.contains(voucherTypeCode)){
            boolean matches = someBusinessType.matches(STR_ENG_PATTERN);
            if (!matches)throw new NuoNiuException("指定业务类型 参数错误");
            sql.WHERE(tableAlias + ".business_type  in ("+someBusinessType+")");

        }
        /******************** 处理 指定 业务类型 结束***********************/
        /*********  获取 单据状态 查询SQL  开始 *******/
        String voucherStateSql = getLastSqlFromVoucherState(tableAlias,dynamicReportDto);
        if(StringUtils.isNotBlank(voucherStateSql)){
            sql.WHERE("("+voucherStateSql+")");
        }
        /*********  获取 单据状态 查询SQL  结束 *******/
        // 特殊处理 ：出入库
        if(mainTableNameStr.equals("scw_delivery_entry_detail iod")){
            //如果没有勾选 主表的字段
            if(null == map){
                sql.INNER_JOIN("scw_delivery_entry iom ON iom.id = iod.order_id" );
            }
            sql.WHERE("iom.data_source = '" + voucherTypeCode + "'");
            sql.SELECT("iom.receive_deliver_type AS iom_receiveDeliverType");
        }

        /******************** 处理  单据数据来源 -- 开始 ***********************/
        String dataSource = dynamicReportDto.getDataSource();
        List<String> dataSourceVoucherList = Arrays.asList(dataSourceVoucherArr);
        if(StringUtils.isNotBlank(dataSource) && dataSourceVoucherList.contains(voucherTypeCode)){
            switch (dataSource){
//                case "DelegateProductionWork": sql.WHERE(tableAlias + ".is_delegate = 1" );break;
//                case "ProductionWork": sql.WHERE(tableAlias + ".is_delegate = 0" );break;
                default: sql.WHERE(tableAlias + ".data_source = '" + dataSource +"'");break;
            }
        }

        /******************** 处理  单据数据来源 -- 结束 ***********************/

        sql.SELECT(StringUtils.join(selectSet,","));
        if(whereSet.size() > 0){
            sql.WHERE(StringUtils.join(whereSet," AND "));
        }
        //排序
        String sortField = dynamicReportDto.getSortField();
        String sortOrder = dynamicReportDto.getSortOrder();
        if(StringUtils.isNoneBlank(sortField) && StringUtils.isNoneBlank(sortOrder) &&  orderFieldSet.contains(sortField)){
            String orderType = " DESC";
            if(StringUtils.isNoneBlank(sortOrder) && "ascend".equals(sortOrder)){
                orderType = " ASC";
            }
            sql.ORDER_BY(sortField + orderType);
        }else{
            sql.ORDER_BY("id DESC");
        }
    }


    /**
     * 分组统计 动态分页查询
     * @param dynamicReportDto
     * @return
     */
    public String statisticReportDynamicSql(DynamicReportDto dynamicReportDto) {
        SQL sql = new SQL();
        dynamicReportDto.setSearchType(1);
        selectStatistic(sql,dynamicReportDto);
        return  sql.toString();
    }

    /**
     * 动态合计
     * @param dynamicReportDto
     * @return
     */
    public String sumStatisticReportDynamicSql(DynamicReportDto dynamicReportDto){
        SQL sql = new SQL();
        dynamicReportDto.setSearchType(2);
        selectStatistic(sql,dynamicReportDto);
        return  sql.toString();
    }
    private void selectStatistic(SQL sql,DynamicReportDto dynamicReportDto){
        List<ErpReportDesign> erpReportDesigns = dynamicReportDto.getErpReportDesignList();
        String someBusinessType = dynamicReportDto.getSomeBusinessType();
        Integer searchType = dynamicReportDto.getSearchType();
        Set<String> selectSet =  new HashSet<>();
        Set<String> whereSet = new HashSet<>();
        Set<String> groupSet = new HashSet<>();
        Set<String> orderFieldSet = new HashSet<>();

        HashMap<Integer,HashMap<String,String>> tableLevelMap = new HashMap<>();


        if (null == erpReportDesigns || erpReportDesigns.size() == 0)throw new NuoNiuException("参数错误");
        ErpReportDesign erpReportDesign = erpReportDesigns.get(0);
        String reportCode = erpReportDesign.getReportCode();
        String[] reportCodeSplit = reportCode.split("_");
        String  voucherTypeCode = reportCodeSplit[0];
        //1.筛选 开启的条件 和 显示的字段
        List<ErpReportDesign> filterDesigns = erpReportDesigns.stream().filter(f-> f.getOnGroup() || f.getOnSearch() || ( f.getIsSummary() && f.getOnSummary() )).collect(Collectors.toList());



        filterDesigns.forEach(item ->{
            String tableNameStr = item.getTableName();
            String columnType = item.getColumnType();
            String code = item.getCode();
            Integer tableLevel = item.getTableLevel();
            Boolean onGroup = item.getOnGroup();
            Boolean onSummary = item.getIsSummary() && item.getOnSummary();
            String summaryType = item.getSummaryType();
            Boolean onSearch = item.getOnSearch();
            String value = item.getValue();
            Object[] rangDateVal = item.getRangDateVal();

//            String beforeLast = StringUtils.substringBeforeLast(tableNameStr, " ");
            String tableAlias = StringUtils.substringAfterLast(tableNameStr, " ");
//            String[] tableNameSplit = tableNameStr.split(" ");
//            String tableAlias = tableNameSplit[1];
            String uncamelCase = StringUtils.uncamelCase(code);

            HashMap<String,String> tableMap = tableLevelMap.get(tableLevel);
            if (null == tableMap){
                tableMap = new HashMap<>();
                tableMap.put("tableName",tableNameStr);
                tableMap.put("joinOn",item.getJoinOn());
                tableMap.put("joinType",item.getJoinType());
                tableLevelMap.put(tableLevel,tableMap);
            }

            String showCode = tableAlias + "_" + code;

            boolean valueNotBlank = StringUtils.isNotBlank(value);
            // 组装 聚合方式
            if(onSummary){
                switch (searchType){
                    case 1:
                            switch (summaryType){
                                case "SUM": selectSet.add("SUM(" + tableAlias +"."+ uncamelCase +") as "+ showCode);break;
                                case "MAX": selectSet.add("MAX(" + tableAlias +"."+ uncamelCase +") as "+ showCode);break;
                                case "MIN": selectSet.add("MIN(" + tableAlias +"."+ uncamelCase +") as "+ showCode);break;
                            }
                            orderFieldSet.add(showCode);
                        break;
                    case 2:
                        selectSet.add("SUM(" + tableAlias +"."+ uncamelCase +") as "+ showCode);
                }
            }

            //a.分组
            if(onGroup){
                if(("模糊搜索".equals(columnType) || "搜索选择".equals(columnType) || "查询下拉".equals(columnType))){
                    String nameField = idFieldToNameField(code);
                    String uncamelCaseName = StringUtils.uncamelCase(nameField);
                    String showNameField =  tableAlias + "_" + nameField;
                    if(StringUtils.isBlank(uncamelCaseName)){
                        uncamelCaseName = uncamelCase;
                    }
                    if(1 == searchType){
                        selectSet.add( tableAlias +"."+ uncamelCaseName +" as "+ showNameField);
                    }

                    groupSet.add(tableAlias +"."+ uncamelCaseName);
                    orderFieldSet.add(showNameField);
                }
                if(1 == searchType){
                    selectSet.add(tableAlias +"."+ uncamelCase +" as "+ showCode);
                }
                groupSet.add(tableAlias +"."+ uncamelCase);
                orderFieldSet.add(showCode);
            }
            //b.查询条件
            if(onSearch){
                if( valueNotBlank){
                    if("文本".equals(columnType) || "区域".equals(columnType) || "快速文本".equals(columnType) ){
                        whereSet.add(tableAlias +"."+ uncamelCase +" LIKE CONCAT('%',#{paramMap."+showCode+"},'%')");
                    }else if("是否".equals(columnType)) {
                        if("false".equals(value)){
                            whereSet.add("("+tableAlias +"."+ uncamelCase +" = 0 OR " +tableAlias +"."+ uncamelCase +" IS NULL)");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" = 1");
                        }
                    }else {
                        whereSet.add(tableAlias +"."+ uncamelCase +" =  #{paramMap."+showCode+"}");
                    }
                }else if(("时间".equals(columnType) || "日期".equals(columnType) || "搜索模糊范围".equals(columnType)) &&  null != rangDateVal && rangDateVal.length == 2 ){
                    ArrayList rangDate = (ArrayList)rangDateVal[1];
                    String startDate = String.valueOf(rangDate.get(0));
                    String endDate =String.valueOf(rangDate.get(1));
                    if(StringUtils.isNoneBlank(startDate)){
                        if("搜索模糊范围".equals(columnType)){
                            whereSet.add("(" + tableAlias +"."+ uncamelCase +" >= #{paramMap."+showCode+"_startDate} OR " + tableAlias +"."+ uncamelCase +" LIKE CONCAT('',#{paramMap."+showCode+"_startDate},'%'))");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" >= #{paramMap."+showCode+"_startDate}");
                        }
                    }
                    if(StringUtils.isNoneBlank(endDate)){
                        if("搜索模糊范围".equals(columnType)){
                            whereSet.add("(" + tableAlias +"."+ uncamelCase +" <= #{paramMap."+showCode+"_endDate} OR " + tableAlias +"."+ uncamelCase +" LIKE CONCAT('',#{paramMap."+showCode+"_endDate},'%'))");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" <= #{paramMap."+showCode+"_endDate}");
                        }
                    }
                }
            }
        } );


        // 3.组装主表
        // 3.1 明细表
        HashMap<String, String> mainTableMap = tableLevelMap.get(1);
        int mapSize =  tableLevelMap.size() + 1;
        String mainTableNameStr = mainTableMap.get("tableName");
        String mianTableAlias =  mainTableNameStr.split(" ")[1];
        sql.FROM(mainTableNameStr);
        sql.WHERE(mianTableAlias + ".company_id = #{companyId} AND "+ mianTableAlias + ".account_id = #{accountId}");

        // 4.组件关联表
        for (int i = 2; i < mapSize ; i++) {
            HashMap<String, String> map = tableLevelMap.get(i);
            String joinType = map.get("joinType");
            String joinOn = map.get("joinOn");
            String tableName = map.get("tableName");
            switch (joinType){
                case "INNER JOIN":sql.INNER_JOIN(tableName + " ON " + joinOn);break;
                case "LEFT JOIN": sql.LEFT_OUTER_JOIN(tableName + " ON " + joinOn);break;
                case "RIGHT JOIN":sql.RIGHT_OUTER_JOIN(tableName + " ON " + joinOn);break;
            }
        }
        // 主表
        HashMap<String, String> map = tableLevelMap.get(2);
        String tableName = map.get("tableName");
        String tableAlias =  tableName.split(" ")[1];
        /******************** 处理 指定 业务类型 开始***********************/
        List<String> noBusinessVoucherList = Arrays.asList(noBusinessVoucherArr);
        if (StringUtils.isNotBlank(someBusinessType) && !noBusinessVoucherList.contains(voucherTypeCode)){
            boolean matches = someBusinessType.matches(STR_ENG_PATTERN);
            if (!matches)throw new NuoNiuException("指定业务类型 参数错误");
            sql.WHERE(tableAlias + ".business_type  in ("+someBusinessType+")");

        }
        /******************** 处理 指定 业务类型 结束***********************/
        /*********  获取 单据状态 查询SQL  开始 *******/
        String voucherStateSql = getLastSqlFromVoucherState(tableAlias,dynamicReportDto);
        if(StringUtils.isNotBlank(voucherStateSql)){
            sql.WHERE("("+voucherStateSql+")");
        }
        /*********  获取 单据状态 查询SQL  结束 *******/
        // 特殊处理 ：出入库
        if(mainTableNameStr.equals("scw_delivery_entry_detail iod")){
            if(null == map){
                sql.INNER_JOIN("scw_delivery_entry iom ON iom.id = iod.order_id" );
            }
            sql.WHERE("iom.data_source = '" + voucherTypeCode + "'");
        }
        /******************** 处理  单据数据来源 -- 开始 ***********************/
        String dataSource = dynamicReportDto.getDataSource();
        List<String> dataSourceVoucherList = Arrays.asList(dataSourceVoucherArr);
        if(StringUtils.isNotBlank(dataSource) && dataSourceVoucherList.contains(voucherTypeCode)){
            switch (dataSource){
//                case "DelegateProductionWork": sql.WHERE(tableAlias + ".is_delegate = 1" );break;
//                case "ProductionWork": sql.WHERE(tableAlias + ".is_delegate = 0" );break;
                default: sql.WHERE(tableAlias + ".data_source = '" + dataSource +"'");break;
            }
        }
        /******************** 处理  单据数据来源 -- 结束 ***********************/

        sql.SELECT(StringUtils.join(selectSet,","));
        if(whereSet.size() > 0){
            sql.WHERE(StringUtils.join(whereSet," AND "));
        }


       switch (searchType){
           case 1:
               //5.分组
               if(groupSet.size() > 0){
                   sql.GROUP_BY(StringUtils.join(groupSet,","));
               }else{
                   throw new NuoNiuException("请勾选分组字段");
               }
               //排序
               String sortField = dynamicReportDto.getSortField();
               String sortOrder = dynamicReportDto.getSortOrder();
               if(StringUtils.isNoneBlank(sortField) && StringUtils.isNoneBlank(sortOrder) &&  orderFieldSet.contains(sortField) ){
                   String orderType = " DESC";
                   if(StringUtils.isNoneBlank(sortOrder) && "ascend".equals(sortOrder)){
                       orderType = " ASC";
                   }
                   sql.ORDER_BY(sortField + orderType);
               }
               break;
           case 2:
               sql.SELECT(mianTableAlias +".company_id AS companyId",mianTableAlias +".account_id AS accountId");
               //5.分组
               sql.GROUP_BY(mianTableAlias+ ".company_id",mianTableAlias+ ".account_id");break;
       }






    }


    /**
     * 获取 单据状态 查询SQL( 选填 触发:voucherStateList,startVoucherState,endVoucherState)
     * @param dynamicReportDto
     * @return
     */
    private String getLastSqlFromVoucherState(String tableAlias,DynamicReportDto dynamicReportDto){
        String voucherStateSql = "";
        List<Integer> voucherStateList = dynamicReportDto.getVoucherStateList();
        Integer startVoucherState = dynamicReportDto.getStartVoucherState();
        Integer endVoucherState = dynamicReportDto.getEndVoucherState();
        if (null != startVoucherState || null != endVoucherState || (null != voucherStateList && voucherStateList.size() > 0)){

            if (null != voucherStateList && voucherStateList.size() > 0){
                String voucherStateListJoinStr = StringUtils.join(voucherStateList, ",");
                voucherStateSql += " OR "+tableAlias+".voucher_state in (" +voucherStateListJoinStr+ ")";
            }
            if(null != startVoucherState && null == endVoucherState ){
                voucherStateSql += " OR "+tableAlias+".voucher_state > " + startVoucherState;
            }
            if(null != endVoucherState && null == startVoucherState ){
                voucherStateSql += " OR "+tableAlias+".voucher_state < " + endVoucherState;
            }
            if(null != startVoucherState && null != endVoucherState){
                voucherStateSql += " OR (" + tableAlias+".voucher_state > " + startVoucherState +" AND "+ tableAlias+".voucher_state < " + endVoucherState +")";
            }
            if (StringUtils.startsWith(voucherStateSql," OR")){
                voucherStateSql = StringUtils.replaceOnce(voucherStateSql," OR","");
            }
        }
        return voucherStateSql;
    }












    /**
     *  字段转换
     * @param idFieldTo
     * @return
     */
    private String idFieldToNameField(String idFieldTo){
        String result = "";
        if(StringUtils.isNoneBlank(idFieldTo) &&  idFieldTo.endsWith(ID_FIELD)){
            String s = idFieldTo.substring(0, idFieldTo.lastIndexOf(ID_FIELD));
            result = s + NAME_FIELD;
        }
        return result;
    }
}
